﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;

namespace Brilliant.ORM
{
    /// <summary>
    /// 查询指令构建类
    /// </summary>
    public class SQL
    {
        private string cmdTextTemp;
        private int recordCount;
        private CommandType cmdType = CommandType.Text;

        /// <summary>
        /// 查询指令
        /// </summary>
        public string CmdText
        {
            set;
            get;
        }

        /// <summary>
        /// 指令类型（默认为Text）
        /// </summary>
        public CommandType CmdType
        {
            get { return cmdType; }
            set { cmdType = value; }
        }

        /// <summary>
        /// 查询语句参数列表
        /// </summary>
        public IDataParameter[] Parameters
        {
            set;
            get;
        }

        /// <summary>
        /// 总记录条数
        /// </summary>
        public int RecordCount
        {
            get { return recordCount; }
        }

        /// <summary>
        /// 构建查询指令参数
        /// </summary>
        /// <param name="cmdText">指令文本</param>
        /// <returns>查询指令参数</returns>
        public static SQL Build(string cmdText)
        {
            SQL sql = new SQL();
            sql.CmdText = cmdText;
            return sql;
        }

        /// <summary>
        /// 构建查询指令参数
        /// </summary>
        /// <param name="cmdText">指令文本</param>
        /// <param name="isProc">是否是存储过程</param>
        /// <returns>查询指令参数</returns>
        public static SQL Build(string cmdText, bool isProc)
        {
            SQL sql = new SQL();
            sql.CmdText = cmdText;
            sql.CmdType = isProc ? CommandType.StoredProcedure : CommandType.Text;
            return sql;
        }

        /// <summary>
        /// 构建查询指令参数
        /// </summary>
        /// <param name="cmdText">指令文本</param>
        /// <param name="parameters">指令参数</param>
        /// <returns>查询指令参数</returns>
        public static SQL Build(string cmdText, IDataParameter[] parameters)
        {
            SQL sql = new SQL();
            sql.CmdText = cmdText;
            sql.Parameters = parameters;
            return sql;
        }

        /// <summary>
        /// 构建查询指令参数
        /// </summary>
        /// <param name="cmdText">指令文本</param>
        /// <param name="parameters">指令参数</param>
        /// <param name="isProc">是否是存储过程</param>
        /// <returns>查询指令参数</returns>
        public static SQL Build(string cmdText, IDataParameter[] parameters, bool isProc)
        {
            SQL sql = new SQL();
            sql.CmdText = cmdText;
            sql.Parameters = parameters;
            sql.CmdType = isProc ? CommandType.StoredProcedure : CommandType.Text;
            return sql;
        }

        /// <summary>
        /// 构建查询指令参数
        /// </summary>
        /// <param name="cmdText">指令文本</param>
        /// <param name="parameters">指令参数</param>
        /// <returns>查询指令参数</returns>
        public static SQL Build(string cmdText, params object[] parameters)
        {
            SQL sql = new SQL();
            int count = Regex.Matches(cmdText, @"\?", RegexOptions.IgnoreCase).Count;
            if (parameters.Length != count)
            {
                throw new Exception("参数长度不符");
            }
            IList<IDataParameter> list = new List<IDataParameter>();
            StringBuilder sb = new StringBuilder();
            string param = "@P";
            for (int i = 0; i < count; i++)
            {
                sb.Clear();
                sb.Append(cmdText);
                cmdText = sb.Replace("?", param + i, cmdText.IndexOf("?"), 1).ToString();
                list.Add(DBHelper.DataProvider.GetParameter(param + i, parameters[i]));
            }
            sql.CmdText = cmdText;
            sql.Parameters = list.ToArray();
            return sql;
        }

        /// <summary>
        /// 构建查询指令参数（存储过程）
        /// </summary>
        /// <param name="cmdText">指令文本</param>
        /// <param name="parameterNames">指令参数名称</param>
        /// <param name="parameters">指令参数</param>
        /// <returns>查询指令参数</returns>
        public static SQL Build(string cmdText, string[] parameterNames, params object[] parameters)
        {
            SQL sql = new SQL();
            sql.CmdText = cmdText;
            sql.CmdType = CommandType.StoredProcedure;
            IList<IDataParameter> list = new List<IDataParameter>();
            for (int i = 0; i < parameterNames.Length; i++)
            {
                list.Add(DBHelper.DataProvider.GetParameter(parameterNames[i], parameters[i]));
            }
            sql.Parameters = list.ToArray();
            return sql;
        }

        /// <summary>
        /// 限制返回的结果记录条数
        /// </summary>
        /// <param name="pageSize">每页显示的记录条数</param>
        /// <param name="pageNumber">当前页码</param>
        public SQL Limit(int pageSize, int pageNumber)
        {
            int startIndex = 0;
            int endIndex = 0;
            this.cmdTextTemp = CmdText;
            string countCmdText = String.Format("SELECT COUNT(*) FROM({0})PT", CmdText);
            this.recordCount = Convert.ToInt32(DBHelper.DataProvider.ExecScalar(SQL.Build(countCmdText)));
            switch (DBHelper.DataPagedType)
            {
                case PagedType.Limit:
                    startIndex = (pageNumber - 1) * pageSize;
                    endIndex = pageSize * pageNumber;
                    CmdText = String.Format("{0} limit {1},{2}", CmdText, startIndex, endIndex);
                    break;
                case PagedType.RowId:
                    startIndex = (pageNumber - 1) * pageSize + 1;
                    endIndex = pageSize * pageNumber;
                    CmdText = String.Format("SELECT * FROM(SELECT ROWNUM RN,PT1.* FROM({0})PT1)PT2 WHERE PT2.RN BETWEEN {1} AND {2}", CmdText, startIndex, endIndex);
                    break;
                case PagedType.RowNumber:
                    startIndex = (pageNumber - 1) * pageSize + 1;
                    endIndex = pageSize * pageNumber;
                    CmdText = String.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RN,PT1.* FROM({0})PT1)PT2 WHERE PT2.RN BETWEEN {1} AND {2}", CmdText, startIndex, endIndex);
                    break;
            }
            return this;
        }

        /// <summary>
        /// 显示查询指令语句
        /// </summary>
        /// <returns>查询指令语句</returns>
        public override string ToString()
        {
            if (!String.IsNullOrEmpty(this.cmdTextTemp))
            {
                return cmdTextTemp;
            }
            return CmdText;
        }
    }
}
